{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>MSHA ID</th>\n",
       "      <th>Mine_Name</th>\n",
       "      <th>Production</th>\n",
       "      <th>Labor_Hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>103381</td>\n",
       "      <td>Tacoa Highwall Miner</td>\n",
       "      <td>56004</td>\n",
       "      <td>22392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013</td>\n",
       "      <td>103404</td>\n",
       "      <td>Reid School Mine</td>\n",
       "      <td>28807</td>\n",
       "      <td>28447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>100759</td>\n",
       "      <td>North River #1 Underground Min</td>\n",
       "      <td>1440115</td>\n",
       "      <td>474784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013</td>\n",
       "      <td>103246</td>\n",
       "      <td>Bear Creek</td>\n",
       "      <td>87587</td>\n",
       "      <td>29193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013</td>\n",
       "      <td>103451</td>\n",
       "      <td>Knight Mine</td>\n",
       "      <td>147499</td>\n",
       "      <td>46393</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  MSHA ID                       Mine_Name  Production  Labor_Hours\n",
       "0  2013   103381            Tacoa Highwall Miner       56004        22392\n",
       "1  2013   103404                Reid School Mine       28807        28447\n",
       "2  2013   100759  North River #1 Underground Min     1440115       474784\n",
       "3  2013   103246                      Bear Creek       87587        29193\n",
       "4  2013   103451                     Knight Mine      147499        46393"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('coalpublic2013.xlsx')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### get the data types "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year            int64\n",
       "MSHA ID         int64\n",
       "Mine_Name      object\n",
       "Production      int64\n",
       "Labor_Hours     int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### read specific columns from a given excel file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MSHA ID</th>\n",
       "      <th>Mine_Name</th>\n",
       "      <th>Labor_Hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>103381</td>\n",
       "      <td>Tacoa Highwall Miner</td>\n",
       "      <td>22392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>103404</td>\n",
       "      <td>Reid School Mine</td>\n",
       "      <td>28447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100759</td>\n",
       "      <td>North River #1 Underground Min</td>\n",
       "      <td>474784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103246</td>\n",
       "      <td>Bear Creek</td>\n",
       "      <td>29193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>103451</td>\n",
       "      <td>Knight Mine</td>\n",
       "      <td>46393</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MSHA ID                       Mine_Name  Labor_Hours\n",
       "0   103381            Tacoa Highwall Miner        22392\n",
       "1   103404                Reid School Mine        28447\n",
       "2   100759  North River #1 Underground Min       474784\n",
       "3   103246                      Bear Creek        29193\n",
       "4   103451                     Knight Mine        46393"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = [1, 2, 4]\n",
    "df = pd.read_excel('coalpublic2013.xlsx', usecols=cols)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write a Pandas program to find the sum, mean, max, min value of 'Production (short tons)' column of coalpublic2013.xlsx file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "'DataFrame' object is not callable",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-12-2cf9e93af7f7>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_excel\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'coalpublic2013.xlsx'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m: 'DataFrame' object is not callable"
     ]
    }
   ],
   "source": [
    "df = pd.read_excel('coalpublic2013.xlsx')\n",
    "df.head()()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sum:  27854323\n",
      "Mean:  557086.46\n",
      "Maximum:  7602722\n",
      "Minimum:  0\n"
     ]
    }
   ],
   "source": [
    "print(\"Sum: \",df[\"Production\"].sum()) \n",
    "print(\"Mean: \",df[\"Production\"].mean())\n",
    "print(\"Maximum: \",df[\"Production\"].max())\n",
    "print(\"Minimum: \",df[\"Production\"].min()) \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### insert a column "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>MSHA ID</th>\n",
       "      <th>Mine_Name</th>\n",
       "      <th>column1</th>\n",
       "      <th>Production</th>\n",
       "      <th>Labor_Hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>103381</td>\n",
       "      <td>Tacoa Highwall Miner</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56004</td>\n",
       "      <td>22392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013</td>\n",
       "      <td>103404</td>\n",
       "      <td>Reid School Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28807</td>\n",
       "      <td>28447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>100759</td>\n",
       "      <td>North River #1 Underground Min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1440115</td>\n",
       "      <td>474784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013</td>\n",
       "      <td>103246</td>\n",
       "      <td>Bear Creek</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87587</td>\n",
       "      <td>29193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013</td>\n",
       "      <td>103451</td>\n",
       "      <td>Knight Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>147499</td>\n",
       "      <td>46393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2013</td>\n",
       "      <td>103433</td>\n",
       "      <td>Crane Central Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>69339</td>\n",
       "      <td>47195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2013</td>\n",
       "      <td>100329</td>\n",
       "      <td>Concord Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>144002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013</td>\n",
       "      <td>100851</td>\n",
       "      <td>Oak Grove Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2269014</td>\n",
       "      <td>1001809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013</td>\n",
       "      <td>102901</td>\n",
       "      <td>Shoal Creek Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>12396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013</td>\n",
       "      <td>102901</td>\n",
       "      <td>Shoal Creek Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1453024</td>\n",
       "      <td>1237415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2013</td>\n",
       "      <td>103180</td>\n",
       "      <td>Sloan Mountain Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>327780</td>\n",
       "      <td>196963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2013</td>\n",
       "      <td>103182</td>\n",
       "      <td>Fishtrap</td>\n",
       "      <td>NaN</td>\n",
       "      <td>175058</td>\n",
       "      <td>87314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2013</td>\n",
       "      <td>103285</td>\n",
       "      <td>Narley Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>154861</td>\n",
       "      <td>90584</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2013</td>\n",
       "      <td>103332</td>\n",
       "      <td>Powhatan Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>140521</td>\n",
       "      <td>61394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2013</td>\n",
       "      <td>103375</td>\n",
       "      <td>Johnson Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>580</td>\n",
       "      <td>1900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2013</td>\n",
       "      <td>103419</td>\n",
       "      <td>Maxine-Pratt Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>125824</td>\n",
       "      <td>107469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2013</td>\n",
       "      <td>103432</td>\n",
       "      <td>Skelton Creek</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8252</td>\n",
       "      <td>220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2013</td>\n",
       "      <td>103437</td>\n",
       "      <td>Black Warrior Mine No 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>145924</td>\n",
       "      <td>70926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2013</td>\n",
       "      <td>102976</td>\n",
       "      <td>Piney Woods Preparation Plant</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>14828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2013</td>\n",
       "      <td>102976</td>\n",
       "      <td>Piney Woods Preparation Plant</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>23193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2013</td>\n",
       "      <td>103380</td>\n",
       "      <td>Calera</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>12621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2013</td>\n",
       "      <td>103380</td>\n",
       "      <td>Calera</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2013</td>\n",
       "      <td>103422</td>\n",
       "      <td>Clark No 1 Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>122727</td>\n",
       "      <td>140250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2013</td>\n",
       "      <td>103467</td>\n",
       "      <td>Helena Surface Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59664</td>\n",
       "      <td>30539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2013</td>\n",
       "      <td>101247</td>\n",
       "      <td>No 4 Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2622528</td>\n",
       "      <td>1551141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2013</td>\n",
       "      <td>101401</td>\n",
       "      <td>No 7 Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5405412</td>\n",
       "      <td>2464719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2013</td>\n",
       "      <td>103172</td>\n",
       "      <td>Searles Mine No. 2, 3, 4, 5, 6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>258078</td>\n",
       "      <td>119542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2013</td>\n",
       "      <td>103179</td>\n",
       "      <td>Fleetwood Mine No 1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75937</td>\n",
       "      <td>63745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2013</td>\n",
       "      <td>103303</td>\n",
       "      <td>Shannon Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>317491</td>\n",
       "      <td>164388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2013</td>\n",
       "      <td>103323</td>\n",
       "      <td>Deerlick Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>133452</td>\n",
       "      <td>46381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2013</td>\n",
       "      <td>103364</td>\n",
       "      <td>Brc Alabama No. 7 Llc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>14324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2013</td>\n",
       "      <td>103436</td>\n",
       "      <td>Swann's Crossing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>137511</td>\n",
       "      <td>77190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2013</td>\n",
       "      <td>100347</td>\n",
       "      <td>Choctaw Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>537429</td>\n",
       "      <td>215295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2013</td>\n",
       "      <td>101362</td>\n",
       "      <td>Manchester Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>219457</td>\n",
       "      <td>116914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2013</td>\n",
       "      <td>102996</td>\n",
       "      <td>Jap Creek Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>375715</td>\n",
       "      <td>164093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2013</td>\n",
       "      <td>103155</td>\n",
       "      <td>Corinth Prep Plant</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>27996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2013</td>\n",
       "      <td>103155</td>\n",
       "      <td>Corinth Prep Plant</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>51994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2013</td>\n",
       "      <td>103195</td>\n",
       "      <td>Mccollum/Sparks Branch Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71910</td>\n",
       "      <td>17411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2013</td>\n",
       "      <td>103342</td>\n",
       "      <td>Reese's Branch Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>263888</td>\n",
       "      <td>115123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2013</td>\n",
       "      <td>103370</td>\n",
       "      <td>Cresent Valley Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2860</td>\n",
       "      <td>621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2013</td>\n",
       "      <td>103372</td>\n",
       "      <td>Cane Creek Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66258</td>\n",
       "      <td>32401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2013</td>\n",
       "      <td>103376</td>\n",
       "      <td>Town Creek</td>\n",
       "      <td>NaN</td>\n",
       "      <td>299167</td>\n",
       "      <td>176499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2013</td>\n",
       "      <td>103389</td>\n",
       "      <td>Carbon Hill Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>76241</td>\n",
       "      <td>84966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2013</td>\n",
       "      <td>103410</td>\n",
       "      <td>Coal Valley Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>407841</td>\n",
       "      <td>158591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2013</td>\n",
       "      <td>103423</td>\n",
       "      <td>Dutton Hill Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37275</td>\n",
       "      <td>9162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2013</td>\n",
       "      <td>1519322</td>\n",
       "      <td>Ghm #25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25054</td>\n",
       "      <td>3108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2013</td>\n",
       "      <td>103321</td>\n",
       "      <td>Poplar Springs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>189370</td>\n",
       "      <td>76366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2013</td>\n",
       "      <td>103358</td>\n",
       "      <td>Old Union</td>\n",
       "      <td>NaN</td>\n",
       "      <td>284563</td>\n",
       "      <td>161805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2013</td>\n",
       "      <td>5000030</td>\n",
       "      <td>Usibelli</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1631584</td>\n",
       "      <td>286079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2013</td>\n",
       "      <td>201195</td>\n",
       "      <td>Kayenta Mine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7602722</td>\n",
       "      <td>1015333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Year  MSHA ID                       Mine_Name  column1  Production  \\\n",
       "0   2013   103381            Tacoa Highwall Miner      NaN       56004   \n",
       "1   2013   103404                Reid School Mine      NaN       28807   \n",
       "2   2013   100759  North River #1 Underground Min      NaN     1440115   \n",
       "3   2013   103246                      Bear Creek      NaN       87587   \n",
       "4   2013   103451                     Knight Mine      NaN      147499   \n",
       "5   2013   103433              Crane Central Mine      NaN       69339   \n",
       "6   2013   100329                    Concord Mine      NaN           0   \n",
       "7   2013   100851                  Oak Grove Mine      NaN     2269014   \n",
       "8   2013   102901                Shoal Creek Mine      NaN           0   \n",
       "9   2013   102901                Shoal Creek Mine      NaN     1453024   \n",
       "10  2013   103180             Sloan Mountain Mine      NaN      327780   \n",
       "11  2013   103182                        Fishtrap      NaN      175058   \n",
       "12  2013   103285                     Narley Mine      NaN      154861   \n",
       "13  2013   103332                   Powhatan Mine      NaN      140521   \n",
       "14  2013   103375                    Johnson Mine      NaN         580   \n",
       "15  2013   103419               Maxine-Pratt Mine      NaN      125824   \n",
       "16  2013   103432                   Skelton Creek      NaN        8252   \n",
       "17  2013   103437         Black Warrior Mine No 1      NaN      145924   \n",
       "18  2013   102976   Piney Woods Preparation Plant      NaN           0   \n",
       "19  2013   102976   Piney Woods Preparation Plant      NaN           0   \n",
       "20  2013   103380                          Calera      NaN           0   \n",
       "21  2013   103380                          Calera      NaN           0   \n",
       "22  2013   103422                 Clark No 1 Mine      NaN      122727   \n",
       "23  2013   103467             Helena Surface Mine      NaN       59664   \n",
       "24  2013   101247                       No 4 Mine      NaN     2622528   \n",
       "25  2013   101401                       No 7 Mine      NaN     5405412   \n",
       "26  2013   103172  Searles Mine No. 2, 3, 4, 5, 6      NaN      258078   \n",
       "27  2013   103179             Fleetwood Mine No 1      NaN       75937   \n",
       "28  2013   103303                    Shannon Mine      NaN      317491   \n",
       "29  2013   103323                   Deerlick Mine      NaN      133452   \n",
       "30  2013   103364           Brc Alabama No. 7 Llc      NaN           0   \n",
       "31  2013   103436                Swann's Crossing      NaN      137511   \n",
       "32  2013   100347                    Choctaw Mine      NaN      537429   \n",
       "33  2013   101362                 Manchester Mine      NaN      219457   \n",
       "34  2013   102996                  Jap Creek Mine      NaN      375715   \n",
       "35  2013   103155              Corinth Prep Plant      NaN           0   \n",
       "36  2013   103155              Corinth Prep Plant      NaN           0   \n",
       "37  2013   103195     Mccollum/Sparks Branch Mine      NaN       71910   \n",
       "38  2013   103342             Reese's Branch Mine      NaN      263888   \n",
       "39  2013   103370             Cresent Valley Mine      NaN        2860   \n",
       "40  2013   103372                 Cane Creek Mine      NaN       66258   \n",
       "41  2013   103376                      Town Creek      NaN      299167   \n",
       "42  2013   103389                Carbon Hill Mine      NaN       76241   \n",
       "43  2013   103410                Coal Valley Mine      NaN      407841   \n",
       "44  2013   103423                Dutton Hill Mine      NaN       37275   \n",
       "45  2013  1519322                         Ghm #25      NaN       25054   \n",
       "46  2013   103321                  Poplar Springs      NaN      189370   \n",
       "47  2013   103358                       Old Union      NaN      284563   \n",
       "48  2013  5000030                        Usibelli      NaN     1631584   \n",
       "49  2013   201195                    Kayenta Mine      NaN     7602722   \n",
       "\n",
       "    Labor_Hours  \n",
       "0         22392  \n",
       "1         28447  \n",
       "2        474784  \n",
       "3         29193  \n",
       "4         46393  \n",
       "5         47195  \n",
       "6        144002  \n",
       "7       1001809  \n",
       "8         12396  \n",
       "9       1237415  \n",
       "10       196963  \n",
       "11        87314  \n",
       "12        90584  \n",
       "13        61394  \n",
       "14         1900  \n",
       "15       107469  \n",
       "16          220  \n",
       "17        70926  \n",
       "18        14828  \n",
       "19        23193  \n",
       "20        12621  \n",
       "21         1402  \n",
       "22       140250  \n",
       "23        30539  \n",
       "24      1551141  \n",
       "25      2464719  \n",
       "26       119542  \n",
       "27        63745  \n",
       "28       164388  \n",
       "29        46381  \n",
       "30        14324  \n",
       "31        77190  \n",
       "32       215295  \n",
       "33       116914  \n",
       "34       164093  \n",
       "35        27996  \n",
       "36        51994  \n",
       "37        17411  \n",
       "38       115123  \n",
       "39          621  \n",
       "40        32401  \n",
       "41       176499  \n",
       "42        84966  \n",
       "43       158591  \n",
       "44         9162  \n",
       "45         3108  \n",
       "46        76366  \n",
       "47       161805  \n",
       "48       286079  \n",
       "49      1015333  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('coalpublic2013.xlsx')\n",
    "df.insert(3, \"column1\", np.nan)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### skipping first twenty rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>2013</th>\n",
       "      <th>102976</th>\n",
       "      <th>Piney Woods Preparation Plant</th>\n",
       "      <th>0</th>\n",
       "      <th>23193</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>103380</td>\n",
       "      <td>Calera</td>\n",
       "      <td>0</td>\n",
       "      <td>12621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013</td>\n",
       "      <td>103380</td>\n",
       "      <td>Calera</td>\n",
       "      <td>0</td>\n",
       "      <td>1402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>103422</td>\n",
       "      <td>Clark No 1 Mine</td>\n",
       "      <td>122727</td>\n",
       "      <td>140250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013</td>\n",
       "      <td>103467</td>\n",
       "      <td>Helena Surface Mine</td>\n",
       "      <td>59664</td>\n",
       "      <td>30539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013</td>\n",
       "      <td>101247</td>\n",
       "      <td>No 4 Mine</td>\n",
       "      <td>2622528</td>\n",
       "      <td>1551141</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   2013  102976 Piney Woods Preparation Plant        0    23193\n",
       "0  2013  103380                        Calera        0    12621\n",
       "1  2013  103380                        Calera        0     1402\n",
       "2  2013  103422               Clark No 1 Mine   122727   140250\n",
       "3  2013  103467           Helena Surface Mine    59664    30539\n",
       "4  2013  101247                     No 4 Mine  2622528  1551141"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('coalpublic2013.xlsx', skiprows = 20)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write a Pandas program to import excel data (coalpublic2013.xlsx ) into a Pandas dataframe and display the last ten rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>MSHA ID</th>\n",
       "      <th>Mine_Name</th>\n",
       "      <th>Production</th>\n",
       "      <th>Labor_Hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27854323</td>\n",
       "      <td>11098816</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  MSHA ID  Mine_Name  Production  Labor_Hours\n",
       "0   NaN      NaN        NaN    27854323     11098816"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('coalpublic2013.xlsx')\n",
    "sum_row=df[[\"Production\", \"Labor_Hours\"]].sum()\n",
    "df_sum=pd.DataFrame(data=sum_row).T\n",
    "df_sum=df_sum.reindex(columns=df.columns)\n",
    "df_sum"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### display the last ten rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>MSHA ID</th>\n",
       "      <th>Mine_Name</th>\n",
       "      <th>Production</th>\n",
       "      <th>Labor_Hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>103381</td>\n",
       "      <td>Tacoa Highwall Miner</td>\n",
       "      <td>56004</td>\n",
       "      <td>22392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013</td>\n",
       "      <td>103404</td>\n",
       "      <td>Reid School Mine</td>\n",
       "      <td>28807</td>\n",
       "      <td>28447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>100759</td>\n",
       "      <td>North River #1 Underground Min</td>\n",
       "      <td>1440115</td>\n",
       "      <td>474784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013</td>\n",
       "      <td>103246</td>\n",
       "      <td>Bear Creek</td>\n",
       "      <td>87587</td>\n",
       "      <td>29193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013</td>\n",
       "      <td>103451</td>\n",
       "      <td>Knight Mine</td>\n",
       "      <td>147499</td>\n",
       "      <td>46393</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  MSHA ID                       Mine_Name  Production  Labor_Hours\n",
       "0  2013   103381            Tacoa Highwall Miner       56004        22392\n",
       "1  2013   103404                Reid School Mine       28807        28447\n",
       "2  2013   100759  North River #1 Underground Min     1440115       474784\n",
       "3  2013   103246                      Bear Creek       87587        29193\n",
       "4  2013   103451                     Knight Mine      147499        46393"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('coalpublic2013.xlsx')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Write a Pandas program to create a subtotal of \"Labor Hours\" against MSHA ID from the excel data (coalpublic2013.xls ). \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Labor_Hours</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MSHA ID</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100329</th>\n",
       "      <td>144002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100347</th>\n",
       "      <td>215295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100759</th>\n",
       "      <td>474784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100851</th>\n",
       "      <td>1001809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101247</th>\n",
       "      <td>1551141</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Labor_Hours\n",
       "MSHA ID             \n",
       "100329        144002\n",
       "100347        215295\n",
       "100759        474784\n",
       "100851       1001809\n",
       "101247       1551141"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('coalpublic2013.xlsx')\n",
    "df_sub=df[[\"MSHA ID\",\"Labor_Hours\"]].groupby('MSHA ID').sum()\n",
    "df_sub.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write a Pandas program to import given excel data (coalpublic2013.xls ) into a dataframe and find a specific MSHA ID\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>MSHA ID</th>\n",
       "      <th>Mine_Name</th>\n",
       "      <th>Production</th>\n",
       "      <th>Labor_Hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013</td>\n",
       "      <td>102901</td>\n",
       "      <td>Shoal Creek Mine</td>\n",
       "      <td>0</td>\n",
       "      <td>12396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013</td>\n",
       "      <td>102901</td>\n",
       "      <td>Shoal Creek Mine</td>\n",
       "      <td>1453024</td>\n",
       "      <td>1237415</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  MSHA ID         Mine_Name  Production  Labor_Hours\n",
       "8  2013   102901  Shoal Creek Mine           0        12396\n",
       "9  2013   102901  Shoal Creek Mine     1453024      1237415"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('coalpublic2013.xlsx')    \n",
    "df[df[\"MSHA ID\"]==102901].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### import given excel data (coalpublic2013.xlsx) into a dataframe and find details where 'Labor Hours' > 20000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>MSHA ID</th>\n",
       "      <th>Mine_Name</th>\n",
       "      <th>Production</th>\n",
       "      <th>Labor_Hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013</td>\n",
       "      <td>103381</td>\n",
       "      <td>Tacoa Highwall Miner</td>\n",
       "      <td>56004</td>\n",
       "      <td>22392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2013</td>\n",
       "      <td>103404</td>\n",
       "      <td>Reid School Mine</td>\n",
       "      <td>28807</td>\n",
       "      <td>28447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>100759</td>\n",
       "      <td>North River #1 Underground Min</td>\n",
       "      <td>1440115</td>\n",
       "      <td>474784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013</td>\n",
       "      <td>103246</td>\n",
       "      <td>Bear Creek</td>\n",
       "      <td>87587</td>\n",
       "      <td>29193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013</td>\n",
       "      <td>103451</td>\n",
       "      <td>Knight Mine</td>\n",
       "      <td>147499</td>\n",
       "      <td>46393</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  MSHA ID                       Mine_Name  Production  Labor_Hours\n",
       "0  2013   103381            Tacoa Highwall Miner       56004        22392\n",
       "1  2013   103404                Reid School Mine       28807        28447\n",
       "2  2013   100759  North River #1 Underground Min     1440115       474784\n",
       "3  2013   103246                      Bear Creek       87587        29193\n",
       "4  2013   103451                     Knight Mine      147499        46393"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('coalpublic2013.xlsx')    \n",
    "df[df[\"Labor_Hours\"] > 20000].head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write a Pandas program to import given excel data (coalpublic2013.xlsx ) into a dataframe and find details where \"Mine Name\" starts with \"P\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>MSHA ID</th>\n",
       "      <th>Mine_Name</th>\n",
       "      <th>Production</th>\n",
       "      <th>Labor_Hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2013</td>\n",
       "      <td>103332</td>\n",
       "      <td>Powhatan Mine</td>\n",
       "      <td>140521</td>\n",
       "      <td>61394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2013</td>\n",
       "      <td>102976</td>\n",
       "      <td>Piney Woods Preparation Plant</td>\n",
       "      <td>0</td>\n",
       "      <td>14828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2013</td>\n",
       "      <td>102976</td>\n",
       "      <td>Piney Woods Preparation Plant</td>\n",
       "      <td>0</td>\n",
       "      <td>23193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2013</td>\n",
       "      <td>103321</td>\n",
       "      <td>Poplar Springs</td>\n",
       "      <td>189370</td>\n",
       "      <td>76366</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Year  MSHA ID                      Mine_Name  Production  Labor_Hours\n",
       "13  2013   103332                  Powhatan Mine      140521        61394\n",
       "18  2013   102976  Piney Woods Preparation Plant           0        14828\n",
       "19  2013   102976  Piney Woods Preparation Plant           0        23193\n",
       "46  2013   103321                 Poplar Springs      189370        76366"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('coalpublic2013.xlsx')    \n",
    "df[df[\"Mine_Name\"].map(lambda x: x.startswith('P'))].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write a Pandas program to import given excel data (coalpublic2013.xls ) into a dataframe and find all records that include two specific MSHA ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>MSHA ID</th>\n",
       "      <th>Mine_Name</th>\n",
       "      <th>Production</th>\n",
       "      <th>Labor_Hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2013</td>\n",
       "      <td>102976</td>\n",
       "      <td>Piney Woods Preparation Plant</td>\n",
       "      <td>0</td>\n",
       "      <td>14828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2013</td>\n",
       "      <td>102976</td>\n",
       "      <td>Piney Woods Preparation Plant</td>\n",
       "      <td>0</td>\n",
       "      <td>23193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2013</td>\n",
       "      <td>103380</td>\n",
       "      <td>Calera</td>\n",
       "      <td>0</td>\n",
       "      <td>12621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2013</td>\n",
       "      <td>103380</td>\n",
       "      <td>Calera</td>\n",
       "      <td>0</td>\n",
       "      <td>1402</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Year  MSHA ID                      Mine_Name  Production  Labor_Hours\n",
       "18  2013   102976  Piney Woods Preparation Plant           0        14828\n",
       "19  2013   102976  Piney Woods Preparation Plant           0        23193\n",
       "20  2013   103380                         Calera           0        12621\n",
       "21  2013   103380                         Calera           0         1402"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df[\"MSHA ID\"].isin([102976,103380])].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### find a list of specified customers by name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "name=[\"Shoal Creek Mine\", \"Piney Woods Preparation Plant\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>MSHA ID</th>\n",
       "      <th>Mine_Name</th>\n",
       "      <th>Production</th>\n",
       "      <th>Labor_Hours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2013</td>\n",
       "      <td>102901</td>\n",
       "      <td>Shoal Creek Mine</td>\n",
       "      <td>0</td>\n",
       "      <td>12396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013</td>\n",
       "      <td>102901</td>\n",
       "      <td>Shoal Creek Mine</td>\n",
       "      <td>1453024</td>\n",
       "      <td>1237415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2013</td>\n",
       "      <td>102976</td>\n",
       "      <td>Piney Woods Preparation Plant</td>\n",
       "      <td>0</td>\n",
       "      <td>14828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2013</td>\n",
       "      <td>102976</td>\n",
       "      <td>Piney Woods Preparation Plant</td>\n",
       "      <td>0</td>\n",
       "      <td>23193</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Year  MSHA ID                      Mine_Name  Production  Labor_Hours\n",
       "8   2013   102901               Shoal Creek Mine           0        12396\n",
       "9   2013   102901               Shoal Creek Mine     1453024      1237415\n",
       "18  2013   102976  Piney Woods Preparation Plant           0        14828\n",
       "19  2013   102976  Piney Woods Preparation Plant           0        23193"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query(f'Mine_Name =={name}').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### find a list of employees where hire_date> 01-01-07"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>emp_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>104</td>\n",
       "      <td>Bruce</td>\n",
       "      <td>Ernst</td>\n",
       "      <td>2007-05-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>107</td>\n",
       "      <td>Diana</td>\n",
       "      <td>Lorentz</td>\n",
       "      <td>2007-02-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>113</td>\n",
       "      <td>Luis</td>\n",
       "      <td>Popp</td>\n",
       "      <td>2007-12-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>119</td>\n",
       "      <td>Karen</td>\n",
       "      <td>Colmenares</td>\n",
       "      <td>2007-08-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    emp_id first_name   last_name  hire_date\n",
       "4      104      Bruce       Ernst 2007-05-21\n",
       "7      107      Diana     Lorentz 2007-02-07\n",
       "13     113       Luis        Popp 2007-12-07\n",
       "19     119      Karen  Colmenares 2007-08-10"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('employee.xlsx')\n",
    "df[df['hire_date'] >='20070101']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### sort the records by the hire_date column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>emp_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>102</td>\n",
       "      <td>Lex</td>\n",
       "      <td>De Haan</td>\n",
       "      <td>2001-01-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>109</td>\n",
       "      <td>Daniel</td>\n",
       "      <td>Faviet</td>\n",
       "      <td>2002-08-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>108</td>\n",
       "      <td>Nancy</td>\n",
       "      <td>Greenberg</td>\n",
       "      <td>2002-08-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>114</td>\n",
       "      <td>Den</td>\n",
       "      <td>Raphaely</td>\n",
       "      <td>2002-12-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>115</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Khoo</td>\n",
       "      <td>2003-05-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>Steven</td>\n",
       "      <td>King</td>\n",
       "      <td>2003-06-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>105</td>\n",
       "      <td>David</td>\n",
       "      <td>Austin</td>\n",
       "      <td>2005-06-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>117</td>\n",
       "      <td>Sigal</td>\n",
       "      <td>Tobias</td>\n",
       "      <td>2005-07-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Neena</td>\n",
       "      <td>Kochhar</td>\n",
       "      <td>2005-09-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>110</td>\n",
       "      <td>John</td>\n",
       "      <td>Chen</td>\n",
       "      <td>2005-09-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>111</td>\n",
       "      <td>Ismael</td>\n",
       "      <td>Sciarra</td>\n",
       "      <td>2005-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>116</td>\n",
       "      <td>Shelli</td>\n",
       "      <td>Baida</td>\n",
       "      <td>2005-12-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Hunold</td>\n",
       "      <td>2006-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>106</td>\n",
       "      <td>Valli</td>\n",
       "      <td>Pataballa</td>\n",
       "      <td>2006-02-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>112</td>\n",
       "      <td>Jose Manuel</td>\n",
       "      <td>Urman</td>\n",
       "      <td>2006-03-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>118</td>\n",
       "      <td>Guy</td>\n",
       "      <td>Himuro</td>\n",
       "      <td>2006-11-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>107</td>\n",
       "      <td>Diana</td>\n",
       "      <td>Lorentz</td>\n",
       "      <td>2007-02-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>104</td>\n",
       "      <td>Bruce</td>\n",
       "      <td>Ernst</td>\n",
       "      <td>2007-05-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>119</td>\n",
       "      <td>Karen</td>\n",
       "      <td>Colmenares</td>\n",
       "      <td>2007-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>113</td>\n",
       "      <td>Luis</td>\n",
       "      <td>Popp</td>\n",
       "      <td>2007-12-07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    emp_id   first_name   last_name  hire_date\n",
       "2      102          Lex     De Haan 2001-01-13\n",
       "9      109       Daniel      Faviet 2002-08-16\n",
       "8      108        Nancy   Greenberg 2002-08-17\n",
       "14     114          Den    Raphaely 2002-12-07\n",
       "15     115    Alexander        Khoo 2003-05-18\n",
       "0      100       Steven        King 2003-06-17\n",
       "5      105        David      Austin 2005-06-25\n",
       "17     117        Sigal      Tobias 2005-07-24\n",
       "1      101        Neena     Kochhar 2005-09-21\n",
       "10     110         John        Chen 2005-09-28\n",
       "11     111       Ismael     Sciarra 2005-09-30\n",
       "16     116       Shelli       Baida 2005-12-24\n",
       "3      103    Alexander      Hunold 2006-01-03\n",
       "6      106        Valli   Pataballa 2006-02-05\n",
       "12     112  Jose Manuel       Urman 2006-03-07\n",
       "18     118          Guy      Himuro 2006-11-15\n",
       "7      107        Diana     Lorentz 2007-02-07\n",
       "4      104        Bruce       Ernst 2007-05-21\n",
       "19     119        Karen  Colmenares 2007-08-10\n",
       "13     113         Luis        Popp 2007-12-07"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = df.sort_values('hire_date')\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Write a Pandas program to import given excel data (employee.xlsx ) into a Pandas dataframe and find a list of employees where hire_date between two specific month and year. Go to Excel data\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>emp_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Neena</td>\n",
       "      <td>Kochhar</td>\n",
       "      <td>2005-09-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Hunold</td>\n",
       "      <td>2006-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>105</td>\n",
       "      <td>David</td>\n",
       "      <td>Austin</td>\n",
       "      <td>2005-06-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>106</td>\n",
       "      <td>Valli</td>\n",
       "      <td>Pataballa</td>\n",
       "      <td>2006-02-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>110</td>\n",
       "      <td>John</td>\n",
       "      <td>Chen</td>\n",
       "      <td>2005-09-28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    emp_id first_name  last_name  hire_date\n",
       "1      101      Neena    Kochhar 2005-09-21\n",
       "3      103  Alexander     Hunold 2006-01-03\n",
       "5      105      David     Austin 2005-06-25\n",
       "6      106      Valli  Pataballa 2006-02-05\n",
       "10     110       John       Chen 2005-09-28"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('employee.xlsx')\n",
    "result = df[(df['hire_date'] >='Jan-2005') & (df['hire_date'] <= 'Dec-2006')].head()\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "emp_id                 int64\n",
       "first_name            object\n",
       "last_name             object\n",
       "hire_date     datetime64[ns]\n",
       "dtype: object"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### find a list of employees of a specified year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>emp_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hire_date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2003-06-17</th>\n",
       "      <td>100</td>\n",
       "      <td>Steven</td>\n",
       "      <td>King</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005-09-21</th>\n",
       "      <td>101</td>\n",
       "      <td>Neena</td>\n",
       "      <td>Kochhar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001-01-13</th>\n",
       "      <td>102</td>\n",
       "      <td>Lex</td>\n",
       "      <td>De Haan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006-01-03</th>\n",
       "      <td>103</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Hunold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007-05-21</th>\n",
       "      <td>104</td>\n",
       "      <td>Bruce</td>\n",
       "      <td>Ernst</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            emp_id first_name last_name\n",
       "hire_date                              \n",
       "2003-06-17     100     Steven      King\n",
       "2005-09-21     101      Neena   Kochhar\n",
       "2001-01-13     102        Lex   De Haan\n",
       "2006-01-03     103  Alexander    Hunold\n",
       "2007-05-21     104      Bruce     Ernst"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('employee.xlsx')\n",
    "df2 = df.set_index(['hire_date'])\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>emp_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hire_date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2005-09-21</th>\n",
       "      <td>101</td>\n",
       "      <td>Neena</td>\n",
       "      <td>Kochhar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005-06-25</th>\n",
       "      <td>105</td>\n",
       "      <td>David</td>\n",
       "      <td>Austin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005-09-28</th>\n",
       "      <td>110</td>\n",
       "      <td>John</td>\n",
       "      <td>Chen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005-09-30</th>\n",
       "      <td>111</td>\n",
       "      <td>Ismael</td>\n",
       "      <td>Sciarra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005-12-24</th>\n",
       "      <td>116</td>\n",
       "      <td>Shelli</td>\n",
       "      <td>Baida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005-07-24</th>\n",
       "      <td>117</td>\n",
       "      <td>Sigal</td>\n",
       "      <td>Tobias</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            emp_id first_name last_name\n",
       "hire_date                              \n",
       "2005-09-21     101      Neena   Kochhar\n",
       "2005-06-25     105      David    Austin\n",
       "2005-09-28     110       John      Chen\n",
       "2005-09-30     111     Ismael   Sciarra\n",
       "2005-12-24     116     Shelli     Baida\n",
       "2005-07-24     117      Sigal    Tobias"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result = df2[\"2005\"]\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### sort based on multiple given columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>emp_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>106</td>\n",
       "      <td>Valli</td>\n",
       "      <td>Pataballa</td>\n",
       "      <td>2006-02-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>Steven</td>\n",
       "      <td>King</td>\n",
       "      <td>2003-06-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>117</td>\n",
       "      <td>Sigal</td>\n",
       "      <td>Tobias</td>\n",
       "      <td>2005-07-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>116</td>\n",
       "      <td>Shelli</td>\n",
       "      <td>Baida</td>\n",
       "      <td>2005-12-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Neena</td>\n",
       "      <td>Kochhar</td>\n",
       "      <td>2005-09-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>108</td>\n",
       "      <td>Nancy</td>\n",
       "      <td>Greenberg</td>\n",
       "      <td>2002-08-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>113</td>\n",
       "      <td>Luis</td>\n",
       "      <td>Popp</td>\n",
       "      <td>2007-12-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>102</td>\n",
       "      <td>Lex</td>\n",
       "      <td>De Haan</td>\n",
       "      <td>2001-01-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>119</td>\n",
       "      <td>Karen</td>\n",
       "      <td>Colmenares</td>\n",
       "      <td>2007-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>112</td>\n",
       "      <td>Jose Manuel</td>\n",
       "      <td>Urman</td>\n",
       "      <td>2006-03-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>110</td>\n",
       "      <td>John</td>\n",
       "      <td>Chen</td>\n",
       "      <td>2005-09-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>111</td>\n",
       "      <td>Ismael</td>\n",
       "      <td>Sciarra</td>\n",
       "      <td>2005-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>118</td>\n",
       "      <td>Guy</td>\n",
       "      <td>Himuro</td>\n",
       "      <td>2006-11-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>107</td>\n",
       "      <td>Diana</td>\n",
       "      <td>Lorentz</td>\n",
       "      <td>2007-02-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>114</td>\n",
       "      <td>Den</td>\n",
       "      <td>Raphaely</td>\n",
       "      <td>2002-12-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>105</td>\n",
       "      <td>David</td>\n",
       "      <td>Austin</td>\n",
       "      <td>2005-06-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>109</td>\n",
       "      <td>Daniel</td>\n",
       "      <td>Faviet</td>\n",
       "      <td>2002-08-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>104</td>\n",
       "      <td>Bruce</td>\n",
       "      <td>Ernst</td>\n",
       "      <td>2007-05-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Hunold</td>\n",
       "      <td>2006-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>115</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Khoo</td>\n",
       "      <td>2003-05-18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    emp_id   first_name   last_name  hire_date\n",
       "6      106        Valli   Pataballa 2006-02-05\n",
       "0      100       Steven        King 2003-06-17\n",
       "17     117        Sigal      Tobias 2005-07-24\n",
       "16     116       Shelli       Baida 2005-12-24\n",
       "1      101        Neena     Kochhar 2005-09-21\n",
       "8      108        Nancy   Greenberg 2002-08-17\n",
       "13     113         Luis        Popp 2007-12-07\n",
       "2      102          Lex     De Haan 2001-01-13\n",
       "19     119        Karen  Colmenares 2007-08-10\n",
       "12     112  Jose Manuel       Urman 2006-03-07\n",
       "10     110         John        Chen 2005-09-28\n",
       "11     111       Ismael     Sciarra 2005-09-30\n",
       "18     118          Guy      Himuro 2006-11-15\n",
       "7      107        Diana     Lorentz 2007-02-07\n",
       "14     114          Den    Raphaely 2002-12-07\n",
       "5      105        David      Austin 2005-06-25\n",
       "9      109       Daniel      Faviet 2002-08-16\n",
       "4      104        Bruce       Ernst 2007-05-21\n",
       "3      103    Alexander      Hunold 2006-01-03\n",
       "15     115    Alexander        Khoo 2003-05-18"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('employee.xlsx')\n",
    "result = df.sort_values(by=['first_name','last_name'],ascending=[0,1])\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### import sheet2 data from a given excel data (employee.xlsx ) into a Pandas dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>emp_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>120</td>\n",
       "      <td>Matthew</td>\n",
       "      <td>Weiss</td>\n",
       "      <td>2004-07-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>121</td>\n",
       "      <td>Adam</td>\n",
       "      <td>Fripp</td>\n",
       "      <td>2005-04-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>122</td>\n",
       "      <td>Payam</td>\n",
       "      <td>Kaufling</td>\n",
       "      <td>2003-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>123</td>\n",
       "      <td>Shanta</td>\n",
       "      <td>Vollman</td>\n",
       "      <td>2005-10-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>124</td>\n",
       "      <td>Kevin</td>\n",
       "      <td>Mourgos</td>\n",
       "      <td>2007-11-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>125</td>\n",
       "      <td>Julia</td>\n",
       "      <td>Nayer</td>\n",
       "      <td>2005-07-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>126</td>\n",
       "      <td>Irene</td>\n",
       "      <td>Mikkilineni</td>\n",
       "      <td>2006-09-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>127</td>\n",
       "      <td>James</td>\n",
       "      <td>Landry</td>\n",
       "      <td>2007-01-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>128</td>\n",
       "      <td>Steven</td>\n",
       "      <td>Markle</td>\n",
       "      <td>2008-03-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>129</td>\n",
       "      <td>Laura</td>\n",
       "      <td>Bissot</td>\n",
       "      <td>2005-08-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>130</td>\n",
       "      <td>Mozhe</td>\n",
       "      <td>Atkinson</td>\n",
       "      <td>2005-10-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>131</td>\n",
       "      <td>James</td>\n",
       "      <td>Marlow</td>\n",
       "      <td>2005-02-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>132</td>\n",
       "      <td>TJ</td>\n",
       "      <td>Olson</td>\n",
       "      <td>2007-04-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>133</td>\n",
       "      <td>Jason</td>\n",
       "      <td>Mallin</td>\n",
       "      <td>2004-06-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>134</td>\n",
       "      <td>Michael</td>\n",
       "      <td>Rogers</td>\n",
       "      <td>2006-08-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>135</td>\n",
       "      <td>Ki</td>\n",
       "      <td>Gee</td>\n",
       "      <td>2007-12-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>136</td>\n",
       "      <td>Hazel</td>\n",
       "      <td>Philtanker</td>\n",
       "      <td>2008-02-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>137</td>\n",
       "      <td>Renske</td>\n",
       "      <td>Ladwig</td>\n",
       "      <td>2003-07-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>138</td>\n",
       "      <td>Stephen</td>\n",
       "      <td>Stiles</td>\n",
       "      <td>2005-10-26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    emp_id first_name    last_name  hire_date\n",
       "0      120    Matthew        Weiss 2004-07-18\n",
       "1      121       Adam        Fripp 2005-04-10\n",
       "2      122      Payam     Kaufling 2003-05-01\n",
       "3      123     Shanta      Vollman 2005-10-10\n",
       "4      124      Kevin      Mourgos 2007-11-16\n",
       "5      125      Julia        Nayer 2005-07-16\n",
       "6      126      Irene  Mikkilineni 2006-09-28\n",
       "7      127      James       Landry 2007-01-14\n",
       "8      128     Steven       Markle 2008-03-08\n",
       "9      129      Laura       Bissot 2005-08-20\n",
       "10     130      Mozhe     Atkinson 2005-10-30\n",
       "11     131      James       Marlow 2005-02-16\n",
       "12     132         TJ        Olson 2007-04-10\n",
       "13     133      Jason       Mallin 2004-06-14\n",
       "14     134    Michael       Rogers 2006-08-26\n",
       "15     135         Ki          Gee 2007-12-12\n",
       "16     136      Hazel   Philtanker 2008-02-06\n",
       "17     137     Renske       Ladwig 2003-07-14\n",
       "18     138    Stephen       Stiles 2005-10-26"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('employee.xlsx',sheet_name=1)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### import three datasheets from a given excel data (coalpublic2013.xlsx ) and combine in to a single dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>emp_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>Steven</td>\n",
       "      <td>King</td>\n",
       "      <td>2003-06-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>Neena</td>\n",
       "      <td>Kochhar</td>\n",
       "      <td>2005-09-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>102</td>\n",
       "      <td>Lex</td>\n",
       "      <td>De Haan</td>\n",
       "      <td>2001-01-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Hunold</td>\n",
       "      <td>2006-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>104</td>\n",
       "      <td>Bruce</td>\n",
       "      <td>Ernst</td>\n",
       "      <td>2007-05-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>105</td>\n",
       "      <td>David</td>\n",
       "      <td>Austin</td>\n",
       "      <td>2005-06-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>106</td>\n",
       "      <td>Valli</td>\n",
       "      <td>Pataballa</td>\n",
       "      <td>2006-02-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>107</td>\n",
       "      <td>Diana</td>\n",
       "      <td>Lorentz</td>\n",
       "      <td>2007-02-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>108</td>\n",
       "      <td>Nancy</td>\n",
       "      <td>Greenberg</td>\n",
       "      <td>2002-08-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>109</td>\n",
       "      <td>Daniel</td>\n",
       "      <td>Faviet</td>\n",
       "      <td>2002-08-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>110</td>\n",
       "      <td>John</td>\n",
       "      <td>Chen</td>\n",
       "      <td>2005-09-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>111</td>\n",
       "      <td>Ismael</td>\n",
       "      <td>Sciarra</td>\n",
       "      <td>2005-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>112</td>\n",
       "      <td>Jose Manuel</td>\n",
       "      <td>Urman</td>\n",
       "      <td>2006-03-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>113</td>\n",
       "      <td>Luis</td>\n",
       "      <td>Popp</td>\n",
       "      <td>2007-12-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>114</td>\n",
       "      <td>Den</td>\n",
       "      <td>Raphaely</td>\n",
       "      <td>2002-12-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>115</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Khoo</td>\n",
       "      <td>2003-05-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>116</td>\n",
       "      <td>Shelli</td>\n",
       "      <td>Baida</td>\n",
       "      <td>2005-12-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>117</td>\n",
       "      <td>Sigal</td>\n",
       "      <td>Tobias</td>\n",
       "      <td>2005-07-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>118</td>\n",
       "      <td>Guy</td>\n",
       "      <td>Himuro</td>\n",
       "      <td>2006-11-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>119</td>\n",
       "      <td>Karen</td>\n",
       "      <td>Colmenares</td>\n",
       "      <td>2007-08-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>120</td>\n",
       "      <td>Matthew</td>\n",
       "      <td>Weiss</td>\n",
       "      <td>2004-07-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>121</td>\n",
       "      <td>Adam</td>\n",
       "      <td>Fripp</td>\n",
       "      <td>2005-04-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>122</td>\n",
       "      <td>Payam</td>\n",
       "      <td>Kaufling</td>\n",
       "      <td>2003-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>123</td>\n",
       "      <td>Shanta</td>\n",
       "      <td>Vollman</td>\n",
       "      <td>2005-10-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>124</td>\n",
       "      <td>Kevin</td>\n",
       "      <td>Mourgos</td>\n",
       "      <td>2007-11-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>125</td>\n",
       "      <td>Julia</td>\n",
       "      <td>Nayer</td>\n",
       "      <td>2005-07-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>126</td>\n",
       "      <td>Irene</td>\n",
       "      <td>Mikkilineni</td>\n",
       "      <td>2006-09-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>127</td>\n",
       "      <td>James</td>\n",
       "      <td>Landry</td>\n",
       "      <td>2007-01-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>128</td>\n",
       "      <td>Steven</td>\n",
       "      <td>Markle</td>\n",
       "      <td>2008-03-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>129</td>\n",
       "      <td>Laura</td>\n",
       "      <td>Bissot</td>\n",
       "      <td>2005-08-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>130</td>\n",
       "      <td>Mozhe</td>\n",
       "      <td>Atkinson</td>\n",
       "      <td>2005-10-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>131</td>\n",
       "      <td>James</td>\n",
       "      <td>Marlow</td>\n",
       "      <td>2005-02-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>132</td>\n",
       "      <td>TJ</td>\n",
       "      <td>Olson</td>\n",
       "      <td>2007-04-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>133</td>\n",
       "      <td>Jason</td>\n",
       "      <td>Mallin</td>\n",
       "      <td>2004-06-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>134</td>\n",
       "      <td>Michael</td>\n",
       "      <td>Rogers</td>\n",
       "      <td>2006-08-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>135</td>\n",
       "      <td>Ki</td>\n",
       "      <td>Gee</td>\n",
       "      <td>2007-12-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>136</td>\n",
       "      <td>Hazel</td>\n",
       "      <td>Philtanker</td>\n",
       "      <td>2008-02-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>137</td>\n",
       "      <td>Renske</td>\n",
       "      <td>Ladwig</td>\n",
       "      <td>2003-07-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>138</td>\n",
       "      <td>Stephen</td>\n",
       "      <td>Stiles</td>\n",
       "      <td>2005-10-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>141</td>\n",
       "      <td>Trenna</td>\n",
       "      <td>Rajs</td>\n",
       "      <td>2003-10-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>142</td>\n",
       "      <td>Curtis</td>\n",
       "      <td>Davies</td>\n",
       "      <td>2005-01-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>143</td>\n",
       "      <td>Randall</td>\n",
       "      <td>Matos</td>\n",
       "      <td>2006-03-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>144</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Vargas</td>\n",
       "      <td>2006-07-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>145</td>\n",
       "      <td>John</td>\n",
       "      <td>Russell</td>\n",
       "      <td>2004-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>146</td>\n",
       "      <td>Karen</td>\n",
       "      <td>Partners</td>\n",
       "      <td>2005-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>147</td>\n",
       "      <td>Alberto</td>\n",
       "      <td>Errazuriz</td>\n",
       "      <td>2005-03-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>148</td>\n",
       "      <td>Gerald</td>\n",
       "      <td>Cambrault</td>\n",
       "      <td>2007-10-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>149</td>\n",
       "      <td>Eleni</td>\n",
       "      <td>Zlotkey</td>\n",
       "      <td>2008-01-29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    emp_id   first_name    last_name  hire_date\n",
       "0      100       Steven         King 2003-06-17\n",
       "1      101        Neena      Kochhar 2005-09-21\n",
       "2      102          Lex      De Haan 2001-01-13\n",
       "3      103    Alexander       Hunold 2006-01-03\n",
       "4      104        Bruce        Ernst 2007-05-21\n",
       "5      105        David       Austin 2005-06-25\n",
       "6      106        Valli    Pataballa 2006-02-05\n",
       "7      107        Diana      Lorentz 2007-02-07\n",
       "8      108        Nancy    Greenberg 2002-08-17\n",
       "9      109       Daniel       Faviet 2002-08-16\n",
       "10     110         John         Chen 2005-09-28\n",
       "11     111       Ismael      Sciarra 2005-09-30\n",
       "12     112  Jose Manuel        Urman 2006-03-07\n",
       "13     113         Luis         Popp 2007-12-07\n",
       "14     114          Den     Raphaely 2002-12-07\n",
       "15     115    Alexander         Khoo 2003-05-18\n",
       "16     116       Shelli        Baida 2005-12-24\n",
       "17     117        Sigal       Tobias 2005-07-24\n",
       "18     118          Guy       Himuro 2006-11-15\n",
       "19     119        Karen   Colmenares 2007-08-10\n",
       "0      120      Matthew        Weiss 2004-07-18\n",
       "1      121         Adam        Fripp 2005-04-10\n",
       "2      122        Payam     Kaufling 2003-05-01\n",
       "3      123       Shanta      Vollman 2005-10-10\n",
       "4      124        Kevin      Mourgos 2007-11-16\n",
       "5      125        Julia        Nayer 2005-07-16\n",
       "6      126        Irene  Mikkilineni 2006-09-28\n",
       "7      127        James       Landry 2007-01-14\n",
       "8      128       Steven       Markle 2008-03-08\n",
       "9      129        Laura       Bissot 2005-08-20\n",
       "10     130        Mozhe     Atkinson 2005-10-30\n",
       "11     131        James       Marlow 2005-02-16\n",
       "12     132           TJ        Olson 2007-04-10\n",
       "13     133        Jason       Mallin 2004-06-14\n",
       "14     134      Michael       Rogers 2006-08-26\n",
       "15     135           Ki          Gee 2007-12-12\n",
       "16     136        Hazel   Philtanker 2008-02-06\n",
       "17     137       Renske       Ladwig 2003-07-14\n",
       "18     138      Stephen       Stiles 2005-10-26\n",
       "0      141       Trenna         Rajs 2003-10-17\n",
       "1      142       Curtis       Davies 2005-01-29\n",
       "2      143      Randall        Matos 2006-03-15\n",
       "3      144        Peter       Vargas 2006-07-09\n",
       "4      145         John      Russell 2004-10-01\n",
       "5      146        Karen     Partners 2005-01-05\n",
       "6      147      Alberto    Errazuriz 2005-03-10\n",
       "7      148       Gerald    Cambrault 2007-10-15\n",
       "8      149        Eleni      Zlotkey 2008-01-29"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_excel('employee.xlsx',sheet_name=0)\n",
    "df2 = pd.read_excel('employee.xlsx',sheet_name=1)\n",
    "df3 = pd.read_excel('employee.xlsx',sheet_name=2)\n",
    "df = pd.concat([df1, df2, df3])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### draw a bar plot where each bar will represent one of the top 10 production"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXAAAAD4CAYAAAD1jb0+AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAARoUlEQVR4nO3de4yldX3H8ffHZQFXYEHAumWpoxVvEV3shGKopEVFWSyN1aZLFI1WaawmoLZUStPW/mVvSLT1shUNiXgXK8ULJaJtaSp0dlluLig2q1XRrUVupTEufPvH8wwMw8zOmZlzDucH71cymXN+z+2bOWc++9vnPM98U1VIktrzmIe7AEnSyhjgktQoA1ySGmWAS1KjDHBJatQ+4zzYYYcdVlNTU+M8pCQ1b9u2bT+uqsPnj481wKemppiZmRnnISWpeUm+s9C4p1AkqVEGuCQ1ygCXpEYZ4JLUqLF+iHn99+9g6h1fWPH2u951yhCrkaS2OQOXpEYtGeBJ9k9ydZJrk9yY5J3zlr83yd2jK1GStJBBTqH8FDixqu5Osha4MsmXqurrSaaBg0dboiRpIUvOwKszO8Ne239VkjXAXwFnj7A+SdIiBjoHnmRNkh3AbuDyqroKeAtwSVXdusS2ZySZSTJz7z13rL5iSRIwYIBX1b1VtQnYCByb5ATgt4D3DrDt1qqarqrpNevWr65aSdL9lnUVSlXdDnwN+DXgqcAtSXYB65LcMvTqJEmLGuQqlMOTHNw/fizwImBbVT2xqqaqagq4p6qeOtpSJUlzDXIVygbgwv5Dy8cAn6qqS0dbliRpKUsGeFVdBxyzxDoHDK0iSdJAxnor/dFHrGfG2+ElaSi8lV6SGmWAS1KjDHBJapQBLkmNMsAlqVEGuCQ1ygCXpEYZ4JLUKANckhplgEtSo5rqSg92ppekWc7AJalRAwd431btmiSX9s8v6DvVX5fkM0n8i4SSNEbLmYGfCeyc8/ytVfXcqnoO8F26HpmSpDEZtKnxRuAU4EOzY1V1Z78swGOBGkWBkqSFDToDPx84G7hv7mCSjwA/BJ7BIg2O7UovSaMxSE/MlwG7q2rb/GVV9Trg5+lOrfz2QtvblV6SRmOQGfjxwKl99/lPACcm+ejswqq6F/gk8IqRVChJWtCSAV5V51TVxr77/BbgCuD0JE+F+8+B/zpw0ygLlSQ92Epv5Aldp/qD+sfXAm8aWlWSpCWlanwXj0xPT9fMzMzYjidJjwRJtlXV9Pxx78SUpEYZ4JLUKANckhplgEtSowxwSWqUAS5JjTLAJalRBrgkNcoAl6RGGeCS1KjmmhovxEbHkh6NnIFLUqMMcElq1CAdefZPcnXfgf7GJO/sxy9KcnOSG5J8OMna0ZcrSZo1yAz8p8CJVfVcYBPw0iTHARfR9cI8mq6p8RtGVqUk6SGW/BCzuj8Yfnf/dG3/VVX1xdl1klwNbBxJhZKkBQ10DjzJmiQ7gN3A5VV11Zxla4HTgS8vsq1d6SVpBAYK8Kq6t6o20c2yj03y7DmL3wf8S1X96yLb2pVekkZgWVehVNXtwNeAlwIk+VPgcOBtQ69MkrRXg1yFcniSg/vHjwVeBNyU5A3AS4DTquq+0ZYpSZpvkDsxN9B1oF9DF/ifqqpLk+wBvgP8exKAi6vqz0dXqiRpLrvSS9KEsyu9JD3CGOCS1CgDXJIaZYBLUqMMcElqlAEuSY0ywCWpUQa4JDXKAJekRhngktSoR0RX+km1612nPNwlSHoEcwYuSY1acYAnOTLJV5Ps7JsdnznMwiRJe7eaUyh7gLdX1fYkBwLbklxeVd8YUm2SpL1Y8Qy8qm6tqu3947uAncARwypMkrR3QzkHnmQKOAa4aoFlNjWWpBFYdYAnOQD4LHBWVd05f7lNjSVpNFYV4EnW0oX3RVV18XBKkiQNYjVXoQS4ANhZVecNryRJ0iBWMwM/HjgdODHJjv5r85DqkiQtYcWXEVbVlUCGWIskaRnGeiv90UesZ8bbyyVpKLyVXpIaZYBLUqMMcElqlAEuSY0ywCWpUQa4JDXKAJekRhngktQoA1ySGmWAS1Kj7Eo/YnamlzQqzsAlqVGrbehwZpIb+q70Zw2rKEnS0lbT0OHZwBuBY4HnAi9LctSwCpMk7d1qZuDPBL5eVfdU1R7gn4GXD6csSdJSVhPgNwAnJDk0yTpgM3Dk/JXsSi9Jo7Gajjw7k/wFcDlwN3AtsGeB9bYCWwH223BUrfR4kqQHW9WHmFV1QVU9r6pOAG4DvjWcsiRJS1nVdeBJnlBVu5P8AvCbwPOHU5YkaSmrvZHns0kOBX4GvLmqfjKEmiRJA1hVgFfVC4ZViCRpeexKL0mN8lZ6SWqUAS5JjTLAJalRBrgkNcoAl6RGGeCS1CgDXJIaZYBLUqMMcElqlAEuSY2yK/3DyI71klbDGbgkNWrgAE+yJsk1SS7tn78wyfYkO5JcmeSpoytTkjTfcmbgZwI75zx/P/CqqtoEfAz442EWJknau4ECPMlG4BTgQ3OGCziof7we+MFwS5Mk7c2gH2KeD5wNHDhn7A3AF5P8H3AncNxCGyY5AzgDYM1Bh6+8UknSgyw5A0/yMmB3VW2bt+itwOaq2gh8BDhvoe2ramtVTVfV9Jp161ddsCSpM8gM/Hjg1CSbgf2Bg5J8AXhGVV3Vr/NJ4MsjqlGStIAlZ+BVdU5VbayqKWALcAXwG8D6JE/rV3sxD/6AU5I0Yiu6kaeq9iR5I11X+vuAnwCvH2plkqS9SlWN7WDT09M1MzMztuNJ0iNBkm1VNT1/3DsxJalRBrgkNcoAl6RGGeCS1CgDXJIaZYBLUqMMcElqlAEuSY0ywCWpUQa4JDXKpsaPEjZQlh55nIFLUqMMcElq1IoDPMnT+470s193JjlrmMVJkha34nPgVXUzsAkgyRrg+8DnhlSXJGkJwzqF8kLg21X1nSHtT5K0hGEF+Bbg4wstSHJGkpkkM/fec8eQDidJWnWAJ9kXOBX49ELL7UovSaMxjBn4ycD2qvrREPYlSRrQMAL8NBY5fSJJGp1VBXiSdcCLgYuHU44kaVCrupW+qu4BDh10/aOPWM+Mt3RL0lB4J6YkNcoAl6RGGeCS1CgDXJIaZYBLUqMMcElqlAEuSY0ywCWpUQa4JDXKAJekRtmVXkuyo700mZyBS1KjlgzwJEcm+WqSnUluTHLmvOW/n6SSHDa6MiVJ8w1yCmUP8Paq2p7kQGBbksur6htJjqT7c7LfHWmVkqSHWHIGXlW3VtX2/vFdwE7giH7xu4GzgRpZhZKkBS3rHHiSKeAY4KokpwLfr6prl9jGpsaSNAIDX4WS5ADgs8BZdKdVzgVOWmq7qtoKbAXYb8NRztQlaUgGmoEnWUsX3hdV1cXALwJPBq5NsgvYCGxP8sRRFSpJerAlZ+BJAlwA7Kyq8wCq6nrgCXPW2QVMV9WPR1SnJGmeQWbgxwOnAycm2dF/bR5xXZKkJSw5A6+qK4Essc7UsAqSJA1mrLfS25VekobHW+klqVEGuCQ1ygCXpEYZ4JLUKANckhplgEtSowxwSWqUAS5JjTLAJalRBrgkNcqu9NKj0C7/pMUjgjNwSWrUirvSJ/mzJN/3T8xK0sNjxV3p+2Xvrqq/Hl15kqTFDPL3wG8Fbu0f35Vkbld6SdLDZMVd6fuhtyS5LsmHkxyyyDZ2pZekERg4wOd2pa+qO4H30zU33kQ3Q/+bhbarqq1VNV1V02vWrR9CyZIkWHlXeqrqR1V1b1XdB/w9cOzoypQkzTfIVSgP6Urfj2+Ys9rLgRuGX54kaTGDXIUy25X++iQ7+rE/Ak5LsgkoYBfwuyOpUJK0oNV0pf/i8MuRJA3KrvSS1ChvpZekRhngktQoA1ySGmWAS1KjDHBJapQBLkmNMsAlqVEGuCQ1ygCXpEbZ1FiSRmxUTaSdgUtSowxwSWrUcjryrElyTZJL++cnJtme5IYkFyYZ6+kYSXq0W84M/ExgJ0CSxwAXAluq6tnAd4DXDr88SdJiBm2pthE4BfhQP3Qo8NOq+mb//HLgFcMvT5K0mEFn4OcDZwP39c9/DKxNMt0/fyVw5EIb2pVekkZjkJ6YLwN2V9W22bGqKmAL8O4kVwN3AXsW2t6u9JI0GoP2xDw1yWZgf+CgJB+tqlcDLwBIchLwtNGVKUmab8kZeFWdU1Ubq2qKbtZ9RVW9OskTAJLsB/wh8IGRVipJepDVXAf+B0l2AtcB/1hVVwypJknSANKdzh6P6enpmpmZGdvxJOmRIMm2qpqeP+6dmJLUKANckhplgEtSowxwSWqUAS5JjRrrVShJ7gJuHtsBV+4wuj8XMOmsc7isc7isc3ieVFWHzx8c95+AvXmhS2EmTZIZ6xwe6xwu6xyuVupciKdQJKlRBrgkNWrcAb51zMdbKescLuscLuscrlbqfIixfogpSRoeT6FIUqMMcElqVVWN/At4Kd3137cA7xjyvj8M7AZumDP2eLo+nd/qvx/Sjwd4T1/HdcDz5mzz2n79bwGvnTP+S8D1/Tbv4YHTTss6Bl3Lua/SNYa+EThzEmula9pxNXBtX+c7+3WfDFzV7+OTwL79+H7981v65VNz6jmnH78ZeMlS74flHgNYA1wDXDqpNfbLdvWvyw5gZkJf94OBzwA30b1Hnz+BNT69/xnOft0JnDVpdY4jU++vd+QH6H7Jvg08BdiXLhieNcT9n9C/uHMD/C/pf+mAdwB/0T/eDHyp/6EfB1w158X5z/77If3j2Rfo6v7NnH7bk1d4jA08EOYHAt8EnjVptfaPD+iXr+3HjgM+BWzpxz8AvKl//HvAB/rHW4BP9o+f1b/W+9GF3rf798Ki74cVHONtwMd4IMAnrsb++S7gsHnv20l73S8E3tAv35cu0CeqxgVy5YfAkya5zpHn68gP0P0wLpvz/BzgnCEfY4oHB/jNwIb+8Qa6G4gAPgicNn894DTgg3PGP9iPbQBumjN+/3rLPcYCNX8eePEk1wqsA7YDv0x3p9o+819T4DLg+f3jffr1Mv91nl1vsfdDv81yjrER+ApwInDpCrYfR42zs7ddPDTAJ+l1/xbw3dl6J7TG+e/Nk4B/m/Q6R/01jnPgRwD/Nef59/qxUfq5qroVoP/+hCVq2dv49xYYX8kx7pdkCjiGbuYzcbUmWZNkB92pqcvpZqO3V9WeuevN30e//A7g0BXUf+gyj/E+4Gzgvn6d5W4/jhoP7ZcV8E9JtiU5ox+bpNf9NrrTER9Jck2SDyV53ITVOP/3aAvw8RXuY6y/76M0jgDPAmM1huMuZLFalju+kmN0C5MDgM8CZ1XVnSvYz8hrrap7q2oT3Sz3WOCZe9n3sOrcW/3zl60D/ruqts0ZW87246hx7rLjq+p5wMnAm5OcsMC6sx6O1z3AM4D3V9UxwP/SnSaYpBrv3ybJvsCpwKdXuI+x/b6P2jgC/Ht0H+DN2gj8YMTH/FGSDQD9991L1LK38Y0LjK/kGCRZSxfeF1XVxZNcK0BV3Q58je7c3sFJ9llgvfv30S9fTzejW279P17mMV6SZBfwCbrTKOdPYI239T/HH/TfdwOfo/tHcZJe98cDt1bVVf3zz9B9rjRJNc7d5mRge1X9aIX7GNvv0KiNI8D/AzgqyZP7fzm3AJeM+JiX0H3KTP/983PGX5POccAd/X+HLgNOSnJIkkPozq9d1i+7K8lxSQK8Zt6+Bj5Gv/0FwM6qOm9SawX2JDkYIMljgRfRXZXwVeCVi+xjdt+vBK6o7mTgJcCWJPsleTJwFN0HRAu+H/ptBj3GpVW1saqm+u2vqKpXTViNV1RVJXlckgP7n+fj6F6vG5bzmozhdb8N2JXk6f3yFwLfmLAaZ48B3bnp2dMnK9nHuOocvXGcaKf7pPabdOdSzx3yvj8O3Ar8jO5fw9+hO/f4FboPZ74CPL5fN8Df9XVcD0zP2c/r6S4FugV43ZzxabpfuG8Df8sDH0wt6xjAr9D91+o6HrgMavOk1Qo8h+7SvOv6ff1Jv+5T6MLtFrr/uu7Xj+/fP7+lX/6UOfWc2+/7ZvpP8/f2fljhMX6VB65Cmbga+/Wv5YHLMs9dyftnDK/7JmCmf93/ge7qjImqsR9fB/wPsH7OvieuznF9eSu9JDXKOzElqVEGuCQ1ygCXpEYZ4JLUKANckhplgEtSowxwSWrU/wO+pB5BaAyHowAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "df = pd.read_excel('coalpublic2013.xlsx')\n",
    "sorted_by_production = df.sort_values(['Production'], ascending=False).head(10)\n",
    "sorted_by_production['Production'].head(10).plot(kind=\"barh\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### draw a bar plot comparing year, MSHA ID, Production and Labor_hours of first ten records. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAABJ0AAAHOCAYAAAAykni5AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nO3df5TeVX0v+veGBAkJ/gBSBAKGelCC+QWMIRZUYigiUFNv5SKgBEW4Vmk9y4sCx1OhemqVtrdLV71SVCS6OEGkIrSKCFhkSfmRRANBEw+oMeYkRQiKCEFI2PePeTJ3wEkyM9nwzCSv11rPmufZ3/397s8zX2aYvJ+9v99Saw0AAAAAtLRTtwsAAAAAYPsjdAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaG9PtAp4ve+21V508eXK3ywAAAADYbixZsuShWuvEgbbtMKHT5MmTs3jx4m6XAQAAALDdKKX8fHPbLK8DAAAAoDmhEwAAAADNCZ0AAAAAaG6HuaYTAAAAMDo99dRTWb16dZ544olul7LD2nXXXTNp0qSMHTt20PsInQAAAIARbfXq1dl9990zefLklFK6Xc4Op9aadevWZfXq1TnwwAMHvZ/ldQAAAMCI9sQTT2TPPfcUOHVJKSV77rnnkGeaCZ0AAACAEU/g1F3D+f4LnQAAAAC2oNaao446Ktdff31f21VXXZXjjjuui1WNfK7pBAAAAIwqk8//RtPjrfzECVvcXkrJJZdckpNOOilz5szJxo0b8+EPfzjf+ta3tmncDRs2ZMyY7TeaMdMJAAAAYCumTp2aP/mTP8knP/nJ/PVf/3VOP/30vPzlL8+CBQsya9aszJw5M+9973vz9NNPJ0nOPvvs9PT05FWvelU++tGP9h1n0qRJ+djHPpYjjzwy11xzTbfezvNi+43TAAAAABq68MILc9hhh2WXXXbJ4sWLc++99+aaa67Jf/zHf2TMmDE5++yzc+WVV+bUU0/NJz7xieyxxx7ZsGFD5syZk7e+9a055JBDkiTjx4/Pbbfd1uV389wTOgEAAAAMwvjx43PyySdnwoQJecELXpCbbropixYtSk9PT5Jk/fr12X///ZMkCxcuzBe+8IVs2LAha9asyY9+9KO+0Onkk0/u2nt4PgmdAAAAAAZpp512yk479V6tqNaad73rXfnYxz72jD733XdfPvWpT+Wuu+7Ki1/84rz97W/PE0880bd9/Pjxz2vN3eKaTgAAAADDcMwxx+Sqq67KQw89lCRZt25dVq1ald/85jfZfffd88IXvjBr167NDTfc0OVKu8NMJwAAAIBhmDZtWi688MIcc8wxefrppzN27Nhccskl6enpySGHHJKpU6fmD//wD3PkkUd2u9SuKLXWbtfwvOjp6amLFy/udhkAAADAEC1fvjxTpkzpdhk7vIHOQyllSa21Z6D+ltcBAAAA0JzQCQAAAIDmXNMJAABgBzBtwbRh7bds/rLGlQA7CjOdAAAAAGhO6AQAAABAc0InAAAAAJoTOgEAAABsRSkl73jHO/peb9iwIRMnTsyJJ56YJHnggQdy4oknZsaMGTnkkENy/PHHJ0lWrlyZqVOnPuNYF110Uf7+7//+Gcfaa6+9csEFF2x2/DPOOCNXX311kuToo4/OK1/5ykyfPj0HH3xwzjnnnPz6179u9l5bcSFxAAAAYHS56EWNj/fIVruMHz8+9957b9avX59x48blxhtvzH777de3/SMf+Uj++I//OO9///uTJPfcc8+gh//2t7+dV77ylbnqqqvy8Y9/PKWUre5zxRVXpKenJ08++WQuuOCCzJs3L9/97ncHPebzwUwnAAAAgEF405velG984xtJkoULF+aUU07p27Z27dpMmjSp7/X06dMHfdyFCxfm/e9/fw444IDccccdQ6ppl112ycUXX5xVq1bl7rvvHtK+zzWhEwAAAMAgvO1tb8uVV16ZJ554Ivfcc0+OOOKIvm3ve9/7cuaZZ2bOnDn5m7/5m6xZs6Zv209+8pPMnDmz73HJJZf0bVu/fn1uvvnmnHjiiTnllFOycOHCIde18847Z8aMGVmxYsW2vcHGhE4AAAAAgzB9+vSsXLkyCxcu7Ltm0yZvfOMb89Of/jRnnXVWVqxYkUMPPTQPPvhgkuTlL395li5d2vd4z3ve07ffv/3bv2XOnDnZbbfd8md/9me55pprsnHjxiHXVmvdtjf3HBA6AQAAAAzSm9/85px77rnPWFq3yR577JFTTz01X/7yl/PqV786t95661aPt3Dhwtx0002ZPHlyDj/88Kxbty7//u//PqSaNm7cmGXLlmXKlClD2u+5JnQCAAAAGKR3vetd+chHPpJp06Y9o/073/lOHn/88STJo48+mp/85Cc54IADtnis3/zmN/ne976XVatWZeXKlVm5cmU+85nPDGmJ3VNPPZULLrgg+++//5CuI/V8EDoBAAAADNKkSZP67lDX35IlS9LT05Pp06fnNa95Td797nfn1a9+9RaP9bWvfS1veMMb8oIXvKCvbd68ebnuuuvyu9/9bov7nnbaaZk+fXqmTp2axx57LNdee+3w3tBzqIzENX/PhZ6enrp48eJulwEAANAV0xZM23qnASybv6xxJTB0y5cvH3FLx3ZEA52HUsqSWmvPQP3NdAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0JzQCQAAAGArdt5558ycOTNTp07NSSedlMcff3zYx7r88stzzjnnDHvfNWvW9L1+97vfnR/96EfDruW5NKbbBQAAAAAMxbQF05oeb9n8ZVvtM27cuCxdujRJctppp+WSSy7JBz7wgb7ttdbUWrPTTs/t/J7LL788U6dOzb777psk+fznP/+cjrctzHQCAAAAGILXvva1uf/++7Ny5cpMmTIl733ve3PYYYflF7/4RRYuXJhp06Zl6tSpOe+88/r2+eIXv5hXvOIVef3rX5/bbrutr/2MM87I1Vdf3fd6woQJfc8vvvjiTJs2LTNmzMj555+fq6++OosXL85pp52WmTNnZv369Tn66KOzePHiJNns2BMmTMiHP/zhzJgxI7Nnz84DDzzwXH57+gidAAAAAAZpw4YNuf766zNtWu9sqx//+Mc5/fTT84Mf/CBjx47Neeedl+985ztZunRpFi1alK9//etZu3ZtLrzwwtx222258cYbB7Uc7vrrr8/Xv/713Hnnnbn77rvzoQ99KG9961vT09OTK664IkuXLs24ceP6+q9Zs2bAsZPksccey+zZs3P33Xfnda97XT73uc89N9+cZxE6AQAAAGzF+vXrM3PmzPT09OSAAw7ImWeemSR52cteltmzZydJFi1alKOPPjoTJ07MmDFjctppp+XWW2/NnXfe2de+yy675OSTT97qeDfddFPe+c53ZrfddkuS7LHHHlvsv7mxk2SXXXbJiSeemCQ5/PDDs3LlyuF+G4bENZ0AAAAAtqL/NZ36Gz9+fN/zWutm9y+lDNg+ZsyYPP300337P/nkk33PN7fPQLY09tixY/uOtfPOO2fDhg2DPu62MNMJAAAAoIEjjjgi3/3ud/PQQw9l48aNWbhwYV7/+tfniCOOyC233JJ169blqaeeyle/+tW+fSZPnpwlS5YkSa699to89dRTSZJjjz02l112Wd9d8h5++OEkye67755HH3100GN3k5lOAAAAAA3ss88++du//dvMmTMntdYcf/zxmTdvXpLkoosuymte85rss88+Oeyww7Jx48YkyVlnnZV58+Zl1qxZmTt3bt/MqeOOOy5Lly5NT09Pdtlllxx//PH5+Mc/njPOOCPvec97Mm7cuNx+++2DGrtbypamX21Penp66qaruQMAAOxohnuL+cHcSh6ea8uXL8+UKVO6XcYOb6DzUEpZUmvtGai/5XUAAAAANCd0AgAAAKA5oRMAAAAAzQmdAAAAAGhO6AQAAABAc0InAAAAAJoTOgEAAADQ3JhuFwAAAAAwFMsPntL0eFNWLN9qnwkTJuS3v/3toI530UUXZcKECTn33HO3tbQt1nD55Zdn8eLF+ad/+qem47RiphMAAADACLFx48btZkyhEwAAAMAw/Ou//muOOOKIHHrooTnmmGPywAMP9G27++6784Y3vCEHHXRQPve5zyVJaq354Ac/mKlTp2batGn5yle+kiS55ZZbMmfOnJx66qmZNm3asGr5+c9/nrlz52b69OmZO3duVq1alSQ544wzcvXVV/f1mzBhwoBjPvbYYznhhBMyY8aMTJ06ta+2bWF5HQAAAMAwHHXUUbnjjjtSSsnnP//5XHzxxfmHf/iHJMk999yTO+64I4899lgOPfTQnHDCCbn99tuzdOnS3H333XnooYfy6le/Oq973euSJHfddVfuvffeHHjggZsdb/369Zk5c2bf64cffjhvfvObkyTnnHNOTj/99MyfPz+XXXZZ/vIv/zJf//rXt1h//zH/5V/+Jfvuu2++8Y1vJEkeeeSRbfreJEInAAAAgGFZvXp1Tj755KxduzZPPvnkMwKjefPmZdy4cRk3blzmzJmTu+66K9/73vdyyimnZOedd87ee++d17/+9Vm0aFFe+MIXZtasWVsMnJJk3LhxWbp0ad/rTdd0SpLbb789X/va15Ik73jHO/KhD31oq/X3H3PatGk599xzc9555+XEE0/Ma1/72iF/P57N8joAAACAYfiLv/iLnHPOOVm2bFn++Z//OU888UTftlLKM/qWUlJr3eyxxo8f37S2TeOPGTMmTz/9dJLe5X1PPvnkgGO+4hWvyJIlSzJt2rRccMEF+ehHP7rNNQidAAAAAIbhkUceyX777ZckWbBgwTO2XXvttXniiSeybt263HLLLX1L6b7yla9k48aNefDBB3Prrbdm1qxZTWr5oz/6o1x55ZVJkiuuuCJHHXVUkmTy5MlZsmRJX01PPfXUgPuvWbMmu+22W97+9rfn3HPPzfe///1trsnyOgAAAGBUmbJi+fM+5uOPP55Jkyb1vf7ABz6Qiy66KCeddFL222+/zJ49Oz/72c/6ts+aNSsnnHBCVq1alb/6q7/Kvvvum7e85S25/fbbM2PGjJRScvHFF+elL31pVqxYsc31ffrTn8673vWu/N3f/V0mTpyYL37xi0mSs846K/PmzcusWbMyd+7czc6oWrZsWT74wQ9mp512ytixY/PZz352m2sqW5ratT3p6empm9Y5AgAA7GimLRjeHbGWzV/WuBIYuuXLl2fKlCndLmOHN9B5KKUsqbX2DNTf8joAAAAAmrO8DgAAAGCEWLduXebOnft77TfffHP23HPPLlQ0fFsNnUop+yf5UpKXJnk6yaW11k+VUvZI8pUkk5OsTPJ/1lp/VXovj/6pJMcneTzJGbXW73eONT/Jf+8c+n/UWhd02g9PcnmScUm+meT9tdY6nDEAAACA7U+t9ffuCLc92nPPPbN06dJul/F7hnN5psEsr9uQ5P+utU5JMjvJ+0ophyQ5P8nNtdaDktzceZ0kb0pyUOdxdpLPJkknQLowyRFJZiW5sJTyks4+n+303bTfcZ32IY0BAAAAbH923XXXrFu3bljBB9uu1pp169Zl1113HdJ+W53pVGtdm2Rt5/mjpZTlSfZLMi/J0Z1uC5LckuS8TvuXau9/CXeUUl5cStmn0/fGWuvDSVJKuTHJcaWUW5K8sNZ6e6f9S0n+NMn1Qx2jUysAAACwHZk0aVJWr16dBx98sNul7LB23XXXZ9y9bzCGdE2nUsrkJIcmuTPJ3ptCnlrr2lLKH3S67ZfkF/12W91p21L76gHaM4wxnhE6lVLOTu9MqBxwwAFDeasAAADACDF27NgceOCB3S6DIRr03etKKROS/EuS/1pr/c2Wug7QVofRvsVyBrNPrfXSWmtPrbVn4sSJWzkkAAAAAK0MKnQqpYxNb+B0Ra31a53mBzrL5tL5+stO++ok+/fbfVKSNVtpnzRA+3DGAAAAAGAE2Gro1LlT3BeSLK+1/j/9Nl2XZH7n+fwk1/ZrP730mp3kkc4SuRuSHFtKeUnnAuLHJrmhs+3RUsrszlinP+tYQxkDAAAAgBFgMNd0OjLJO5IsK6Vsumfff0vyiSRXlVLOTLIqyUmdbd9McnyS+5M8nuSdSVJrfbiU8rEkizr9PrrpouJJ/jzJ5UnGpfcC4td32oc0BgAAAAAjw2DuXve9DHwNpSSZO0D/muR9mznWZUkuG6B9cZKpA7SvG+oYAAAAAHTfoC8kDgAAAACDJXQCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0JzQCQAAAIDmhE4AAAAANCd0AgAAAKA5oRMAAAAAzQmdAAAAAGhO6AQAAABAc0InAAAAAJoTOgEAAADQnNAJAAAAgOaETgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0JzQCQAAAIDmhE4AAAAANCd0AgAAAKA5oRMAAAAAzQmdAAAAAGhO6AQAAABAc0InAAAAAJoTOgEAAADQnNAJAAAAgOaETgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0JzQCQAAAIDmhE4AAAAANCd0AgAAAKA5oRMAAAAAzQmdAAAAAGhO6AQAAABAc0InAAAAAJoTOgEAAADQnNAJAAAAgOaETgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgua2GTqWUy0opvyyl3Nuv7aJSyv8upSztPI7vt+2CUsr9pZQfl1Le2K/9uE7b/aWU8/u1H1hKubOUcl8p5SullF067S/ovL6/s33y1sYAAAAAYGQYzEyny5McN0D7P9ZaZ3Ye30ySUsohSd6W5FWdff7fUsrOpZSdk3wmyZuSHJLklE7fJPlk51gHJflVkjM77Wcm+VWt9b8k+cdOv82OMbS3DQAAAMBzaauhU6311iQPD/J485JcWWv9Xa31Z0nuTzKr87i/1vrTWuuTSa5MMq+UUpK8IcnVnf0XJPnTfsda0Hl+dZK5nf6bGwMAAACAEWJbrul0Tinlns7yu5d02vZL8ot+fVZ32jbXvmeSX9daNzyr/RnH6mx/pNN/c8cCAAAAYIQYbuj02SQvTzIzydok/9BpLwP0rcNoH86xfk8p5exSyuJSyuIHH3xwoC4AAAAAPAeGFTrVWh+otW6stT6d5HP5/5e3rU6yf7+uk5Ks2UL7Q0leXEoZ86z2Zxyrs/1F6V3mt7ljDVTnpbXWnlprz8SJE4fzVgEAAAAYhmGFTqWUffq9fEuSTXe2uy7J2zp3njswyUFJ7kqyKMlBnTvV7ZLeC4FfV2utSf49yVs7+89Pcm2/Y83vPH9rku90+m9uDAAAAABGiDFb61BKWZjk6CR7lVJWJ7kwydGllJnpXda2Msn/lSS11h+WUq5K8qMkG5K8r9a6sXOcc5LckGTnJJfVWn/YGeK8JFeWUv5Hkh8k+UKn/QtJvlxKuT+9M5zetrUxAAAAABgZSu/koe1fT09PXbx4cbfLAAAA6IppC6YNa79l85c1rgTYnpRSltRaewbati13rwMAAACAAQmdAAAAAGhO6AQAAABAc1u9kDgAAAAAw7MjX0/NTCcAAAAAmhM6AQAAANCc5XUA22BHnioLAACwJWY6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0JzQCQAAAIDmhE4AAAAANCd0AgAAAKA5oRMAAAAAzQmdAAAAAGhO6AQAAABAc0InAAAAAJoTOgEAAADQnNAJAAAAgOaETgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0JzQCQAAAIDmhE4AAAAANCd0AgAAAKC5Md0uAAAAAIBnWn7wlGHtN2XF8saVDJ+ZTgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0JzQCQAAAIDmhE4AAAAANCd0AgAAAKA5oRMAAAAAzQmdAAAAAGhO6AQAAABAc0InAAAAAJoTOgEAAADQnNAJAAAAgOaETgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0NxWQ6dSymWllF+WUu7t17ZHKeXGUsp9na8v6bSXUsqnSyn3l1LuKaUc1m+f+Z3+95VS5vdrP7yUsqyzz6dLKWW4YwAAAAAwMgxmptPlSY57Vtv5SW6utR6U5ObO6yR5U5KDOo+zk3w26Q2QklyY5Igks5JcuClE6vQ5u99+xw1nDAAAAABGjq2GTrXWW5M8/KzmeUkWdJ4vSPKn/dq/VHvdkeTFpZR9krwxyY211odrrb9KcmOS4zrbXlhrvb3WWpN86VnHGsoYAAAAAIwQY4a539611rVJUmtdW0r5g077fkl+0a/f6k7bltpXD9A+nDHWPrvIUsrZ6Z0NlQMOOGCIbxEAAIDlB08Z1n5TVixvXAkw2rS+kHgZoK0Oo304Y/x+Y62X1lp7aq09EydO3MphAQAAAGhluKHTA5uWtHW+/rLTvjrJ/v36TUqyZivtkwZoH84YAAAAAIwQww2drkuy6Q5085Nc26/99M4d5mYneaSzRO6GJMeWUl7SuYD4sUlu6Gx7tJQyu3PXutOfdayhjAEAAADACLHVazqVUhYmOTrJXqWU1em9C90nklxVSjkzyaokJ3W6fzPJ8UnuT/J4kncmSa314VLKx5Is6vT7aK1108XJ/zy9d8gbl+T6ziNDHQMAAACAkWOroVOt9ZTNbJo7QN+a5H2bOc5lSS4boH1xkqkDtK8b6hgAAAAAjAytLyQOAAAAAEInAAAAANoTOgEAAADQnNAJAAAAgOaETgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0JzQCQAAAIDmhE4AAAAANCd0AgAAAKA5oRMAAAAAzQmdAAAAAGhO6AQAAABAc0InAAAAAJoTOgEAAADQnNAJAAAAgOaETgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0JzQCQAAAIDmhE4AAAAANCd0AgAAAKA5oRMAAAAAzQmdAAAAAGhO6AQAAABAc0InAAAAAJoTOgEAAADQnNAJAAAAgOaETgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0JzQCQAAAIDmhE4AAAAANCd0AgAAAKA5oRMAAAAAzQmdAAAAAGhO6AQAAABAc0InAAAAAJoTOgEAAADQnNAJAAAAgOaETgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmtum0KmUsrKUsqyUsrSUsrjTtkcp5cZSyn2dry/ptJdSyqdLKfeXUu4ppRzW7zjzO/3vK6XM79d+eOf493f2LVsaAwAAAICRocVMpzm11pm11p7O6/OT3FxrPSjJzZ3XSfKmJAd1Hmcn+WzSGyAluTDJEUlmJbmwX4j02U7fTfsdt5UxAAAAABgBnovldfOSLOg8X5DkT/u1f6n2uiPJi0sp+yR5Y5Iba60P11p/leTGJMd1tr2w1np7rbUm+dKzjjXQGAAAAACMANsaOtUk3y6lLCmlnN1p27vWujZJOl//oNO+X5Jf9Nt3dadtS+2rB2jf0hjPUEo5u5SyuJSy+MEHHxzmWwQAAABgqMZs4/5H1lrXlFL+IMmNpZQVW+hbBmirw2gftFrrpUkuTZKenp4h7QsAAADA8G3TTKda65rO118muSa912R6oLM0Lp2vv+x0X51k/367T0qyZivtkwZozxbGAAAAAGAEGHboVEoZX0rZfdPzJMcmuTfJdUk23YFufpJrO8+vS3J65y52s5M80lkad0OSY0spL+lcQPzYJDd0tj1aSpnduWvd6c861kBjAAAAADACbMvyur2TXNObB2VMkv9Za/1WKWVRkqtKKWcmWZXkpE7/byY5Psn9SR5P8s4kqbU+XEr5WJJFnX4frbU+3Hn+50kuTzIuyfWdR5J8YjNjAAAAADACDDt0qrX+NMmMAdrXJZk7QHtN8r7NHOuyJJcN0L44ydTBjgEwWiw/eMqw9puyYnnjSgAAAJ4b23r3OgAAAAD4PUInAAAAAJoTOjDLgYoAAAlfSURBVAEAAADQnNAJAAAAgOaETgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0JzQCQAAAIDmhE4AAAAANCd0AgAAAKA5oRMAAAAAzQmdAAAAAGhO6AQAAABAc0InAAAAAJoTOgEAAADQnNAJAAAAgOaETgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADQndAIAAACgOaETAAAAAM0JnQAAAABoTugEAAAAQHNCJwAAAACaEzoBAAAA0JzQCQAAAIDmhE4AAAAANCd0AgAAAKA5oRMAAAAAzQmdAAAAAGhO6AQAAABAc0InAAAAAJoTOgEAAADQnNAJAAAAgOaETgAAAAA0J3QCAAAAoDmhEwAAAADNCZ0AAAAAaE7oBAAAAEBzQicAAAAAmhM6AQAAANDcmG4XAAAwVNMWTBvWfsvmL2tcCTBYyw+eMqz9pqxY3rgSAJ4vZjoBAAAA0JzQCQAAAIDmhE4AAAAANCd0AgAAAKA5oRMAAAAAzQmdAAAAAGhO6AQAAABAc2O6XQDPctGLhrnfI23rYHicv9FruOfuwAPa1sHw+NkbvfzsjW5+9kY352/08rtzdPOzN3r52RsyM50AAAAAaM5MJwB2WNMWTBvWfsvmL2tcCQAAbH/MdAIAAACgOaETAAAAAM0JnQAAAABozjWdAAB4XrmeGgDsGMx0AgAAAKA5M522Ez4xBAAAAEYSM50AAAAAaM5MJwBgh7H84CnD2m/KiuWNK2E4nD8AGF3MdAIAAACgOaETAAAAAM1ZXgcjwHAvBH/V324Y1n6WGcC2scQHAAC2Tui0g/MPJwAAAOC5MKqX15VSjiul/LiUcn8p5fxu1wMAAABAr1E706mUsnOSzyT54ySrkywqpVxXa/1RdysDAACAkcHqFrppNM90mpXk/lrrT2utTya5Msm8LtcEAAAAQJJSa+12DcNSSnlrkuNqre/uvH5HkiNqref063N2krM7L1+Z5MfPe6HPn72SPNTtIhg252/0cu5GN+dv9HLuRjfnb3Rz/kYv5250c/5Gr+393L2s1jpxoA2jdnldkjJA2zMStFrrpUkufX7K6a5SyuJaa0+362B4nL/Ry7kb3Zy/0cu5G92cv9HN+Ru9nLvRzfkbvXbkczeal9etTrJ/v9eTkqzpUi0AAAAA9DOaQ6dFSQ4qpRxYStklyduSXNflmgAAAADIKF5eV2vdUEo5J8kNSXZOclmt9YddLqubdohlhNsx52/0cu5GN+dv9HLuRjfnb3Rz/kYv5250c/5Grx323I3aC4kDAAAAMHKN5uV1AAAAAIxQQicAAAAAmhM6AQAAANDcqL2Q+I6ulHJwknlJ9ktSk6xJcl2tdXlXC4PtXOdnb78kd9Zaf9uv/bha67e6VxmDUUqZlaTWWheVUg5JclySFbXWb3a5NIaolPKlWuvp3a6DoSulHJVkVpJ7a63f7nY9bF4p5Ygky2utvymljEtyfpLDkvwoycdrrY90tUC2qJTyl0muqbX+otu1MDT97s6+ptZ6Uynl1CR/lGR5kktrrU91tUC2qpTy8iRvSbJ/kg1J7kuycEf8velC4qNQKeW8JKckuTLJ6k7zpPT+Yrqy1vqJbtXGtimlvLPW+sVu18HAOn+8vS+9/8OfmeT9tdZrO9u+X2s9rJv1sWWllAuTvCm9H7jcmOSIJLckOSbJDbXWv+ledWxJKeW6ZzclmZPkO0lSa33z814Ug1ZKuavWOqvz/Kz0/h69JsmxSf7V3y0jVynlh0lmdO4afWmSx5NcnWRup/3/6GqBbFEp5ZEkjyX5SZKFSb5aa32wu1UxGKWUK9L798puSX6dZEKSr6X3Z6/UWud3sTy2ovNvhj9J8t0kxydZmuRX6Q2h3ltrvaV71T3/hE6jUCnlfyV51bMT7k4i/sNa60HdqYxtVUpZVWs9oNt1MLBSyrIkr6m1/raUMjm9f3h/udb6qVLKD2qth3a1QLaoc/5mJnlBkv9MMqnfp/d31lqnd7VANquU8v30zqz4fHpn95b0/gPqbUlSa/1u96pja/r/fiylLEpyfK31wVLK+CR31FqndbdCNqeUsrzWOqXz/BkfrpRSltZaZ3avOramlPKDJIen98OVk5O8OcmS9P7+/Fqt9dEulscWlFLuqbVOL6WMSfK/k+xba91YSilJ7vY3y8i26W/OzjnbLck3a61Hl1IOSHLtjvZvBsvrRqenk+yb5OfPat+ns40RrJRyz+Y2Jdn7+ayFIdt505K6WuvKUsrRSa4upbwsveePkW1DrXVjksdLKT+ptf4mSWqt60spfneObD1J3p/kw0k+WGtdWkpZL2waNXYqpbwkvdcSLZtmWtRaHyulbOhuaWzFvf1mYd9dSumptS4upbwiieU9I1+ttT6d5NtJvl1KGZveGb+nJPn7JBO7WRxbtFNnQsH49M52elGSh9P7wdnYbhbGoI1JsjG952z3JKm1rur8HO5QhE6j039NcnMp5b4km9ZoH5DkvyQ5p2tVMVh7J3ljeqdY9leS/MfzXw5D8J+llJm11qVJ0pnxdGKSy5L4pH7ke7KUslut9fH0fvKbJCmlvCgC+xGt84+mfyylfLXz9YH4G2Y0eVF6Z1eUJLWU8tJa63+WUiZEYD/SvTvJp0op/z3JQ0luL6X8Ir1/f767q5UxGM/4+eqskrguyXWdWb6MXF9IsiLJzun9wOWrpZSfJpmd3kusMLJ9PsmiUsodSV6X5JNJUkqZmN7wcIdied0oVUrZKb0X4dwvvf9DWZ1kUedTfEawUsoXknyx1vq9Abb9z1rrqV0oi0EopUxK72yZ/xxg25G11tu6UBaDVEp5Qa31dwO075Vkn1rrsi6UxTCUUk5IcmSt9b91uxaGr7PkYO9a68+6XQtbVkrZPckfpjfsXV1rfaDLJTEIpZRX1Fr/V7frYHhKKfsmSa11TSnlxeldJrmq1npXdytjMEopr0oyJb03zVjR7Xq6SegEAAAAQHM7dbsAAAAAALY/QicAAAAAmhM6AQAAANCc0AkAAACA5oROAAAAADT3/wF9/1xyx7zVEwAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 1440x576 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.read_excel('coalpublic2013.xlsx')\n",
    "df.head(10).plot(kind='bar', figsize=(20,8))\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### export the result "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df1 = pd.read_excel('E:\\employee.xlsx',sheet_name=0)\n",
    "# df2 = pd.read_excel('E:\\employee.xlsx',sheet_name=1)\n",
    "# df3 = pd.read_excel('E:\\employee.xlsx',sheet_name=2)\n",
    "# df = pd.concat([df1, df2, df3])\n",
    "# df.to_excel('e:\\output.xlsx', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
